package com.gxuwz.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.gxuwz.core.db.DBUtil;
import com.gxuwz.pojo.DataEntity;
import com.gxuwz.pojo.Form;
import com.gxuwz.pojo.SysUser;
import com.gxuwz.pojo.Topic;

@Repository("dataDAO")
public class DataDAO {
	//addList
	public void addDatas(String tableName, List<DataEntity> list, int fieldNum){
		Connection conn = DBUtil.getConnection();
		StringBuffer sql1 = new StringBuffer("insert ignore into ");
		sql1.append(tableName);
		StringBuffer sql2 = new StringBuffer(" (userId, userName, ");
		for(int i=0;i<list.get(0).getDatas().length;i++){
			sql2.append(" ,data");
			sql2.append(i);
		}
		sql2.append(" ) values");
		StringBuffer sql3 = new StringBuffer(" ");
		int j=0;
		for(DataEntity dataEntity:list){
			j++;
			String[] datas = dataEntity.getDatas();
			sql3.append("( '");
			sql3.append(dataEntity.getUserId());
			sql3.append("','");
			sql3.append(dataEntity.getUserName());
			sql3.append("'");
			for(String dataValue:datas){
				sql3.append(",'");
				sql3.append(dataValue);
				sql3.append("'");
			}
			
			if(j == list.size()){
				sql3.append(" );");
			}else{
				sql3.append("),");
			}
		}
		sql1.append(sql2);
		sql1.append(sql3);
		PreparedStatement ptmt;
		try {
			ptmt = conn.prepareStatement(sql1.toString());
			ptmt.execute();
			ptmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	public void addData(String tableName, Map<String,String> map){
		Connection conn = DBUtil.getConnection();
		StringBuffer sql1 = new StringBuffer("insert into ");
		sql1.append(tableName);
		StringBuffer sql2 = new StringBuffer(" ( data_state");
		StringBuffer sql3 = new StringBuffer(" values( '1'");
		
		for(Map.Entry<String,String> entry : map.entrySet()) { 
			sql2.append(",");
			sql2.append(entry.getKey());
			
			sql3.append(",'");
			sql3.append(entry.getValue());
			sql3.append("'");
		}
		sql2.append(")");
		sql3.append(")");
		sql1.append(sql2);
		sql1.append(sql3);
		PreparedStatement ptmt;
		try {
			ptmt = conn.prepareStatement(sql1.toString());
			ptmt.execute();
			ptmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	//添加填写者
	public void addWriter(String tableName, List<SysUser> writers){
		Connection conn = DBUtil.getConnection();
		StringBuffer sql = new StringBuffer("insert ignore into ");
		sql.append(tableName);
		sql.append(" (user_id,user_name) ");
		
		for(int i=0;i<writers.size();i++){
			sql.append(" select '"
					+ writers.get(i).getUserId()
					+ "','"
					+ writers.get(i).getUserName()
					+ "' ");
			if(i<writers.size()-1){
				sql.append(" union all ");
			}
		}
		PreparedStatement ptmt;
		try {
			ptmt = conn.prepareStatement(sql.toString());
			ptmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	//edit
	public void update(String tableName, Map<String,String> map){
		Connection conn = DBUtil.getConnection();
		StringBuffer sql2 = new StringBuffer(" where id=");
		StringBuffer sql = new StringBuffer("update ");
		sql.append(tableName);
		sql.append(" set data_state=1");
		
		for(Map.Entry<String,String> entry : map.entrySet()) { 
			if("id".equals(entry.getKey())){
				sql2.append(entry.getValue());
			}else{
				sql.append(", ");
				sql.append(entry.getKey());
				sql.append(" = '");
				sql.append(entry.getValue());
				sql.append("'");
			}
		}
		sql.append(sql2);
		PreparedStatement ptmt;
		try {
			ptmt = conn.prepareStatement(sql.toString());
			ptmt.execute();
			ptmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		
	}
	
	//暂时作废，参数无法确定
//	public void update(String tableName, DataEntity dataEntity){
//		String[] datas = dataEntity.getDatas();
//		Connection conn = DBUtil.getConnection();
//		StringBuffer sql = new StringBuffer("update ");
//		sql.append(tableName);
//		sql.append(" set data_state=1");
//		for(int i=0;i<dataEntity.getDatas().length;i++){
//			sql.append(", data");
//			sql.append(i);
//			sql.append(" = '");
//			sql.append(datas[i]);
//			sql.append("' ");
//		}
//		sql.append(" where id=");
//		sql.append(dataEntity.getId());
//		PreparedStatement ptmt;
//		try {
//			ptmt = conn.prepareStatement(sql.toString());
//			ptmt.execute();
//			ptmt.close();
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
//	}

	//del
	
	//find

	/**
	 * 模糊查询
	 * @param tableName
	 * @param dataEntity
	 * @param topicList
	 * @return
	 */
	public List<DataEntity> find(String tableName, DataEntity dataEntity, List<Topic> topicList){
		List<DataEntity> dataList=new ArrayList<DataEntity>();
		
		Connection conn=DBUtil.getConnection();
		StringBuilder sb=new StringBuilder("select * from ");
		sb.append(tableName);
		sb.append(" where 1=1 ");
		if(dataEntity != null){
			//模糊查询部分
			if(dataEntity.getUserId()!=null && !"".equals(dataEntity.getUserId())){
				sb.append(" and user_id like '%");
				sb.append(dataEntity.getUserId());
				sb.append("%' ");
			}
			if(dataEntity.getUserName()!=null && !"".equals(dataEntity.getUserName())){
				sb.append(" and user_name like '%");
				sb.append(dataEntity.getUserName());
				sb.append("%' ");
			}
			if(dataEntity.getDataState()!=null && !"".equals(dataEntity.getDataState())){
				sb.append(" and data_state = ");
				sb.append(dataEntity.getDataState());
			}
			if(dataEntity.getDatas() != null && dataEntity.getDatas().length>0){
				String[] ds = dataEntity.getDatas();
				for(int i=0;i<topicList.size();i++){
					if(ds[i] != null && !"".equals(ds[i])){
						sb.append(" and data"
								+ topicList.get(i).getId()
								+ " like '%"
								+ ds[i]
								+ "%' ");
					}

				}
			}
		}
		try{
			System.out.println("输出语句"+sb.toString());
			
			PreparedStatement ptmt=conn.prepareStatement(sb.toString());
			
			ResultSet rs=ptmt.executeQuery();
			
			DataEntity data=null;
			
			while(rs.next()){
				data = new DataEntity();
				data.setId(rs.getInt("id"));
				data.setUserId(rs.getString("user_id"));
				data.setUserName(rs.getString("user_name"));
				data.setUpdateTime(rs.getTimestamp("update_time"));
				data.setDataState(rs.getInt("data_state"));
				
				String[] das = new String[topicList.size()];
				for(int i=0;i<topicList.size();i++){
					das[i] = rs.getString("data"+topicList.get(i).getId());
				}
				data.setDatas(das);
				dataList.add(data);
			}
		}catch(Exception e){
			System.out.println(e);
		}
		return dataList;
	}
	
	
	//creatTable 普通表格，限制防止重复提交
	public void createDB(String tableName, List<Topic> topicList){
		Connection conn = DBUtil.getConnection();
		StringBuffer sql = new StringBuffer("create table ");
		sql.append(tableName);
		sql.append(" ( id INT(11) not null AUTO_INCREMENT,"
				+ " data_state int(3) default 0,"
				+ " user_id varchar(50) ,"
				+ " user_name varchar(20) ,"
				+ " update_time datetime ,");
		for(Topic t : topicList){
			sql.append("data");
			sql.append(t.getId());
			if(t.getTopicType() != 2){
				sql.append(" varchar(200) ,");
			}else{
				sql.append(" varchar(1000) ,");
			}
			
		}
		sql.append("unique (user_id) , primary key (id) ) ");
		PreparedStatement ptmt;
		try {
			ptmt = conn.prepareStatement(sql.toString());
			ptmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	//长期收
	public void createDBT(String tableName, List<Topic> topicList){
		Connection conn = DBUtil.getConnection();
		StringBuffer sql = new StringBuffer("create table ");
		sql.append(tableName);
		sql.append(" ( id INT(11) not null AUTO_INCREMENT,"
				+ " data_state int(3) default 0,"
				+ " user_id varchar(50) ,"
				+ " user_name varchar(20) ,"
				+ " update_time datetime ,");
		for(Topic t : topicList){
			sql.append("data");
			sql.append(t.getId());
			if(t.getTopicType() != 2){
				sql.append(" varchar(200) ,");
			}else{
				sql.append(" varchar(1000) ,");
			}
			
		}
		sql.append(" primary key (id) ) ");
		PreparedStatement ptmt;
		try {
			ptmt = conn.prepareStatement(sql.toString());
			ptmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	/**
	 * 获得填写者
	 * @param dataFormName
	 * @return
	 */
	public List<SysUser> getWriter(String tableName){
		List<SysUser> userList=new ArrayList<SysUser>();
		Connection conn=DBUtil.getConnection();
		StringBuilder sb=new StringBuilder("select * from ");
		sb.append(tableName);
		try{
			PreparedStatement ptmt=conn.prepareStatement(sb.toString());
			ResultSet rs=ptmt.executeQuery();
			SysUser sysUser=null;
			while(rs.next()){
				sysUser = new SysUser();
				sysUser.setUserId(rs.getString("user_id"));
				sysUser.setUserName(rs.getString("user_name"));
				userList.add(sysUser);
			}
		}catch(Exception e){
			System.out.println(e);
		}
		return userList;
	}

	/**
	 * 查询是否填写人
	 * @param dataFormName
	 * @param writerId
	 * @return
	 */
	public boolean isWriter(String tableName, String userId){
		Connection conn=DBUtil.getConnection();
		StringBuilder sb=new StringBuilder("select * from ");
		sb.append(tableName);
		sb.append(" where user_id='");
		sb.append(userId);
		sb.append("' ");
		PreparedStatement ptmt;
		try {
			ptmt = conn.prepareStatement(sb.toString());
			ResultSet rs=ptmt.executeQuery();
			if(rs.next()){
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return false;
	}
	
	/**
	 * 修改表格
	 * @param tableName
	 * @param topicList
	 */
	public void editTable(String tableName, List<Topic> topicList){
		StringBuffer addSql = new StringBuffer("ALTER TABLE ");
		addSql.append(tableName);
		for(int i=0;i<topicList.size();i++){
			addSql.append(" ADD ");
			addSql.append("data"+topicList.get(i).getId());
			addSql.append(" varchar(200)");
			if(i == topicList.size()-1){
				addSql.append(";");
			}else{
				addSql.append(",");
			}
			
		}
		Connection conn = DBUtil.getConnection();
		PreparedStatement ptmt;
		try {
			ptmt = conn.prepareStatement(addSql.toString());
			ptmt.execute();
			ptmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 复制数据
	 * @param newName
	 * @param oldName
	 * @param newTopic
	 * @param oldTopic
	 */
	public void copyData(String newName, String oldName, List<Topic> newTopic, List<Topic> oldTopic){
		Connection conn = DBUtil.getConnection();
		StringBuffer sql = new StringBuffer("INSERT INTO ");
		sql.append(newName);
		sql.append("(user_id,user_name,update_time,");
		
		for(int i=0;i<newTopic.size();i++){
			sql.append("data"+newTopic.get(i).getId());
			if(i != newTopic.size()-1){
				sql.append(",");
			}else{
				sql.append(")");
			}
		}
		
		sql.append(" SELECT user_id,user_name,update_time,");
		for(int j=0;j<oldTopic.size();j++){
			sql.append("data"+oldTopic.get(j).getId());
			if(j != oldTopic.size()-1){
				sql.append(",");
			}
		}
		sql.append(" FROM ");
		sql.append(oldName);
		System.out.println(sql.toString());
		PreparedStatement ptmt;
		try {

			ptmt = conn.prepareStatement(sql.toString());
			ptmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 获得需要发送邮件提醒的用户
	 * @param tableName
	 * @return
	 */
	public List<SysUser> getMailUser(String tableName){
		List<SysUser> userList = new ArrayList<SysUser>();
		Connection conn = DBUtil.getConnection();
		StringBuffer sql = new StringBuffer("select * from ");
		sql.append(tableName);
		sql.append(" inner join sys_user on ");
		sql.append(tableName);
		sql.append(".user_id = sys_user.user_id and ");
		sql.append(tableName);
		sql.append(".data_state = 0");
		try{
			PreparedStatement ptmt=conn.prepareStatement(sql.toString());
			ResultSet rs=ptmt.executeQuery();
			SysUser sysUser = null;
			while(rs.next()){
				sysUser = new SysUser();
				sysUser.setUserId(rs.getString("user_id"));
				sysUser.setUserName(rs.getString("user_name"));
				sysUser.setUserEmail(rs.getString("user_email"));
				userList.add(sysUser);
			}
		}catch(Exception e){
			System.out.println(e);
		}
		
		return userList;
	}
	
}
